unit clsMovim;

interface

uses Dm, dbtables, biblio;

type
  TMovim = class(TObject)
  private
    Mov_Codigo: Double;
  public
    Hist_Codigo: string;
    Mov_DataTic: TDateTime;
    Mov_DataMov: TDateTime;
    Mov_Historico: string;
    Mov_Tic: string;
    Mov_ImpCheque: string;
    Mov_Contab: string;
    Mov_Valor: Double;
    Mov_Favorecido: string;
    Mov_NumCheque: integer;
    Mov_Grupo: string;
    Mov_CMC7: string;
    Conta_Codigo: string;
    Ped_Numero: string;
    xMov_Codigo: string;
    function Inclui: Double;
    function Gravar(p_Mov_Codigo: Double): Byte;
    function Recuperar(p_Mov_Codigo: Double): Byte;
    function RecuperarLanc(p_Conta, p_NumCheque: string; p_Vlr: Double): Byte;
    function Excluir(p_Mov_Codigo: Double): Byte;
    function Gravar_Ticagem(p_Mov_Codigo: Integer; p_Mov_Tic: string; p_Mov_DataTic: tdatetime): Boolean;
    procedure Consultar(DataIni, Datafim: TDateTime; var p_q: TQuery);
    procedure Lista_Ticagem(var p_q: TQuery; p_conta, Tic: string; p_DataIni, p_DataFim: TDateTime);
    function recuperaLancPedidoVenda(var p_q: TQuery; pPed1, pPed2: string): Boolean;
    function eliminaLancPedidoVenda(pPed1, pPed2: string): Boolean;
    function Ultimo: Integer;
  end;

implementation

uses DB, menu, SysUtils;

function TMovim.Inclui;
var
  tq: TQuery;
begin
  tq := Tquery.Create(DmInt);
  tq.databasename := dmint.Db.DatabaseName;
  tq.active := False;
  tq.sql.clear;
  tq.params.clear;
  tq.sql.add('SELECT max(Mov_Codigo) as ultimo FROM Movim');
  tq.open;
  if tq.FieldByName('Ultimo').Asstring = '' then
    result := 1
  else
    result := tq.FieldByName('Ultimo').AsFloat + 1;
  tq.close;
  tq.free;
end;

function TMovim.Gravar;
var
  tq: TQuery;
begin
  tq := Tquery.Create(DmInt);
  tq.databasename := dmint.Db.DatabaseName;
  tq.active := False;
  tq.sql.clear;
  tq.params.clear;
  tq.sql.add('SELECT * FROM Movim where Mov_Codigo=:k_Mov_Codigo');
  tq.parambyname('k_Mov_Codigo').asfloat := p_Mov_Codigo;
  tq.open;
  if tq.eof then begin
    tq.sql.clear;
    tq.params.clear;
    tq.sql.add('INSERT INTO Movim VALUES(' +
      ':k_Mov_Codigo,' +
      ':k_Hist_Codigo,' +
      ':k_Mov_DataTic,' +
      ':k_Mov_DataMov,' +
      ':k_Mov_Historico,' +
      ':k_Mov_Tic,' +
      ':k_Mov_impcheque,' +
      ':k_Mov_Contab,' +
      ':k_Mov_Valor,' +
      ':k_Mov_Favorecido,' +
      ':k_Mov_NumCheque,' +
      ':k_Mov_Grupo,' +
      ':k_Conta_Codigo,' +
      ':k_Mov_CMC7, ');
    if Trim(Ped_Numero) <> '' then
      tq.SQL.Add(':k_Ped_Numero)')
    else
      tq.SQL.Add('null)');
  end else begin
    tq.sql.clear;
    tq.params.clear;
    tq.sql.add('UPDATE Movim SET ' +
      'Hist_Codigo   =:k_Hist_Codigo,' +
      'Mov_DataTic   =:k_Mov_DataTic,' +
      'Mov_DataMov   =:k_Mov_DataMov,' +
      'Mov_Historico =:k_Mov_Historico,' +
      'Mov_Tic       =:k_Mov_Tic,' +
      'Mov_ImpCheque =:k_Mov_impcheque,' +
      'Mov_Contab    =:k_Mov_Contab,' +
      'Mov_Valor     =:k_Mov_Valor,' +
      'Mov_Favorecido=:k_Mov_Favorecido,' +
      'Mov_NumCheque =:k_Mov_NumCheque,' +
      'Conta_Codigo  =:k_Conta_Codigo,' +
      'Mov_Grupo     =:k_Mov_Grupo, ' +
      'Mov_CMC7      =:k_Mov_CMC7, ');
    if Trim(Ped_Numero) <> '' then
      tq.SQL.Add('Ped_Numero = :k_Ped_Numero ')
    else
      tq.SQL.Add('Ped_Numero = null ');
    tq.SQL.Add('where Mov_Codigo=:k_Mov_Codigo');
  end;
  tq.parambyname('k_Mov_Codigo').asfloat := p_mov_codigo;
  tq.parambyname('k_Hist_codigo').asstring := Hist_Codigo;
  tq.parambyname('k_Conta_Codigo').asstring := strzero(Conta_Codigo, 3);
  tq.parambyname('k_Mov_DataTic').asdatetime := Mov_DataTic;
  tq.parambyname('k_Mov_DataMov').asdatetime := Mov_DataMov;
  tq.parambyname('k_Mov_Historico').asstring := copy(Mov_Historico, 1, 150);
  tq.parambyname('k_Mov_Tic').asstring := Mov_Tic;
  tq.parambyname('k_Mov_ImpCheque').asstring := Mov_ImpCheque;
  tq.parambyname('k_Mov_Contab').asstring := Mov_Contab;
  tq.parambyname('k_Mov_Valor').asfloat := Mov_Valor;
  tq.parambyname('k_Mov_Favorecido').asstring := copy(Mov_Favorecido, 1, 60);
  tq.parambyname('k_Mov_NumCheque').asinteger := Mov_NumCheque;
  tq.parambyname('k_Mov_Grupo').asstring := Mov_Grupo;
  tq.parambyname('k_Mov_CMC7').asstring := Mov_CMC7;
  if trim(Ped_Numero) <> '' then
    tq.ParamByName('k_Ped_Numero').AsString := Ped_Numero;
  tq.close;
  GeraLog('MOVIM', 'Gravar');
  tq.execsql;
  result := 1;
  tq.close;
  tq.Free;
end;

function TMovim.Recuperar;
var
  tq: TQuery;
begin
  tq := Tquery.Create(dmint);
  tq.databasename := DmInt.Db.DatabaseName;
  tq.active := False;
  tq.sql.clear;
  tq.sql.add('SELECT * FROM Movim where Mov_Codigo=:k_Mov_Codigo');
  tq.parambyname('k_Mov_Codigo').asfloat := p_Mov_Codigo;
  tq.open;
  Mov_Codigo := p_Mov_Codigo;
  if tq.eof then begin
    Hist_Codigo := '';
    Conta_Codigo := '';
    Mov_DataTic := 0;
    Mov_DataMov := 0;
    Mov_Historico := '';
    Mov_Tic := '';
    Mov_ImpCheque := '';
    Mov_Contab := '';
    Mov_Valor := 0;
    Mov_Favorecido := '';
    Mov_NumCheque := 0;
    Mov_Grupo := '';
    Mov_CMC7 := '';
    Ped_Numero := '';
    Result := 0;
  end else begin
    Hist_Codigo := tq.fieldbyname('Hist_Codigo').asstring;
    Conta_Codigo := tq.fieldbyname('Conta_Codigo').asstring;
    Mov_DataTic := tq.fieldbyname('Mov_DataTic').asdatetime;
    Mov_DataMov := tq.fieldbyname('Mov_DataMov').asdatetime;
    Mov_Historico := tq.fieldbyname('Mov_Historico').asstring;
    Mov_Tic := tq.fieldbyname('Mov_Tic').asstring;
    Mov_ImpCheque := tq.fieldbyname('Mov_ImpCheque').asstring;
    Mov_Contab := tq.fieldbyname('Mov_Contab').asstring;
    Mov_Valor := tq.fieldbyname('Mov_Valor').asfloat;
    Mov_Favorecido := tq.fieldbyname('Mov_Favorecido').asstring;
    Mov_NumCheque := tq.fieldbyname('Mov_NumCheque').asinteger;
    Mov_Grupo := tq.fieldbyname('Mov_Grupo').asstring;
    Mov_CMC7 := tq.fieldbyname('Mov_CMC7').asstring;
    Ped_Numero := tq.FieldByName('Ped_Numero').AsString;
    Result := 1;
  end;
  tq.Close;
  tq.Free;
end;

function TMovim.Excluir;
var
  tq: TQuery;
begin
  tq := Tquery.Create(dmint);
  tq.databasename := dmint.db.databasename;
  tq.active := False;
  tq.close;
  tq.sql.Clear;
  tq.params.clear;
  tq.sql.add('DELETE FROM Movim where Mov_Codigo=:k_Mov_Codigo');
  tq.parambyname('k_Mov_Codigo').asfloat := p_Mov_Codigo;
  GeraLog('MOVIM', 'Excluir');
  tq.execSQL;
  Result := 1;
  tq.close;
  tq.Free;
end;

procedure TMovim.Consultar;
begin
  p_q.close;
  p_q.sql.Clear;
  p_q.Params.Clear;
  p_q.sql.add('Select * from Movim where mov_datamov>=:k_dataini and mov_datamov<=:k_datafim ' +
    'order by Mov_Codigo');
  p_q.parambyname('k_dataini').asdatetime := DataIni;
  p_q.parambyname('k_datafim').asdatetime := DataFim;
  p_q.open;
end;

procedure TMovim.Lista_Ticagem;
begin
  p_q.Close;
  p_q.sql.Clear;
  p_q.Params.Clear;
  if tic = ' ' then
    p_q.sql.add('Select m.Mov_Codigo,m.Mov_DataMov,m.Mov_Tic,m.Hist_Codigo,m.Mov_NumCheque,m.Mov_Historico,' +
      'm.Mov_Valor,h.Hist_Descricao from Movim m,Historico h where Conta_Codigo=:a ' +
      'and mov_DataMov>=:b and mov_DataMov<=:c and m.Hist_Codigo=h.Hist_Codigo ')
  else
    p_q.sql.add('Select m.Mov_Codigo,m.Mov_DataMov,m.Mov_Tic,m.Hist_Codigo,m.Mov_NumCheque,m.Mov_Historico,' +
      'm.Mov_Valor,h.Hist_Descricao from Movim m ,Historico h where Conta_Codigo=:a ' +
      'and mov_datamov>=:b and mov_datamov<=:c and Mov_Tic=:d and m.Hist_Codigo=h.Hist_Codigo ');
  if FileExists(caminho_personal + 'ferrari') then
    p_q.sql.add('order by Mov_datamov,Mov_Codigo')
  else
    p_q.sql.add('order by Mov_Codigo,Mov_datamov');
  p_q.parambyname('a').asstring := p_Conta;
  p_q.parambyname('b').asdatetime := p_DataIni;
  p_q.parambyname('c').asdatetime := p_DataFim;
  if tic <> ' ' then
    p_q.parambyname('d').asstring := Tic;
  p_q.open;
end;

function TMovim.Gravar_Ticagem;
var
  tq: Tquery;
begin
  tq := Tquery.Create(dm.dmint);
  tq.databasename := dm.dmint.db.databasename;
  tq.active := False;
  tq.sql.Clear;
  tq.params.clear;
  tq.sql.add('Update Movim set Mov_Tic=:p_Tic,Mov_DataTic=:p_Data where Mov_Codigo=:p_Codigo');
  tq.parambyname('p_Tic').asstring := p_Mov_Tic;
  tq.parambyname('p_Data').asdatetime := p_Mov_DataTic;
  tq.parambyname('p_Codigo').asinteger := p_Mov_Codigo;
  try
    GeraLog('MOVIM', 'Gravar_Ticagem');
    tq.execsql;
    result := true;
  except
    result := false;
  end;
  tq.close;
  tq.free;
end;

function TMovim.Ultimo: Integer;
var
  tq: Tquery;
begin
  tq := Tquery.Create(dm.dmint);
  tq.databasename := dm.dmint.db.databasename;
  tq.active := False;
  tq.sql.Clear;
  tq.params.clear;
  tq.sql.add('Select max(Mov_Codigo) Ultimo from Movim');
  tq.Open;
  if tq.FieldByName('Ultimo').IsNull then
    Result := 1
  else
    Result := tq.FieldByName('Ultimo').AsInteger + 1;
  tq.Close;
  tq.Free;
end;

function TMovim.RecuperarLanc;
var
  tq: tquery;
begin
  tq := Tquery.Create(dmint);
  tq.databasename := DmInt.Db.DatabaseName;
  tq.active := False;
  tq.sql.clear;
  tq.sql.add('SELECT * FROM Movim where Mov_NumCheque=:k_Mov_Codigo ' +
    'and Conta_codigo =:Conta and Mov_Valor =:Valor');
  tq.parambyname('k_Mov_Codigo').AsString := p_NumCheque;
  tq.parambyname('conta').AsString := p_Conta;
  tq.parambyname('valor').AsFloat := p_Vlr;
  tq.open;
  if tq.eof then begin
    xMov_Codigo := '';
    Hist_Codigo := '';
    Conta_Codigo := '';
    Mov_DataTic := 0;
    Mov_DataMov := 0;
    Mov_Historico := '';
    Mov_Tic := '';
    Mov_ImpCheque := '';
    Mov_Contab := '';
    Mov_Valor := 0;
    Mov_Favorecido := '';
    Mov_NumCheque := 0;
    Mov_Grupo := '';
    Mov_CMC7 := '';
    Ped_Numero := '';
    Result := 0;
  end else begin
    xMov_Codigo := tq.fieldbyname('mov_codigo').AsString;
    Hist_Codigo := tq.fieldbyname('Hist_Codigo').asstring;
    Conta_Codigo := tq.fieldbyname('Conta_Codigo').asstring;
    Mov_DataTic := tq.fieldbyname('Mov_DataTic').asdatetime;
    Mov_DataMov := tq.fieldbyname('Mov_DataMov').asdatetime;
    Mov_Historico := tq.fieldbyname('Mov_Historico').asstring;
    Mov_Tic := tq.fieldbyname('Mov_Tic').asstring;
    Mov_ImpCheque := tq.fieldbyname('Mov_ImpCheque').asstring;
    Mov_Contab := tq.fieldbyname('Mov_Contab').asstring;
    Mov_Valor := tq.fieldbyname('Mov_Valor').asfloat;
    Mov_Favorecido := tq.fieldbyname('Mov_Favorecido').asstring;
    Mov_NumCheque := tq.fieldbyname('Mov_NumCheque').asinteger;
    Mov_Grupo := tq.fieldbyname('Mov_Grupo').asstring;
    Mov_CMC7 := tq.fieldbyname('Mov_CMC7').asstring;
    Ped_Numero := tq.FieldByName('Ped_Numero').AsString;
    Result := 1;
  end;
  tq.Close;
  tq.Free;

end;

function TMovim.recuperaLancPedidoVenda(var p_q: TQuery; pPed1,
  pPed2: string): Boolean;
begin
  p_q.Close;
  p_q.sql.Clear;
  p_q.Params.Clear;
  p_q.sql.add('Select Mov_Codigo from Movim where Ped_Numero =:ped1 or Ped_Numero =:ped2');
  p_q.parambyname('ped1').AsString := pPed1;
  p_q.parambyname('ped2').AsString := pPed2;
  p_q.open;
  Result := not p_q.Eof;
end;

function TMovim.eliminaLancPedidoVenda(pPed1, pPed2: string): Boolean;
var
  tq: TQuery;
begin
  tq := Tquery.Create(DmInt);
  tq.databasename := dmint.Db.DatabaseName;
  tq.active := False;
  tq.sql.clear;
  tq.params.clear;
  tq.sql.add('Delete from Movim where Ped_Numero =:ped1 or Ped_Numero =:ped2');
  tq.parambyname('ped1').AsString := pPed1;
  tq.parambyname('ped2').AsString := pPed2;
  try
    tq.ExecSQL;
  finally
    tq.close;
  end;
  Result := tq.RowsAffected > 0;
  tq.free;
end;

end.

